package cn.mapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.pojo.User;
import cn.util.DBUtil;
import cn.util.EncodeUtil;

public class UserMapperImp extends DBUtil implements UserMapper {
	// @Test
	// public void a() {
	// System.out.println(getUsersById("1"));
	// }

	@Override
	public Integer totalCount(String userName) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet re = null;
		String sql = "select count(id) from smbms_user where 1=1 ";
		List<Object> param = new ArrayList<Object>();
		if (userName != null && !userName.equals("")) {
			sql += "and userName like ?;";
			param.add("%" + userName + "%");
		}
		System.out.println(sql);
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			re = ps.executeQuery();
			if (re.next()) {
				return re.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, re);
		}
		return 0;
	}

	@Override
	public List<User> getUserLike(String userName, Integer rowNum, int pagecount) {
		String sql = " select * from `smbms_user` where 1=1  ";
		List<Object> list = new ArrayList<Object>();
		if (userName != null && !userName.equals("")) {
			sql += " and userName like ? ";
			list.add("%" + userName + "%");
		}
		sql += "  limit ?,? ;";
		list.add(rowNum);
		list.add(pagecount);
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		System.out.println(sql);
		List<User> userList = new ArrayList<User>();
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject((i + 1), list.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				User user = new User();
				user.setAddress(st.getString("address"));
				user.setBirthday(st.getTimestamp("birthday"));
				user.setCreatedBy(st.getLong("createdBy"));
				user.setCreationDate(st.getTimestamp("creationDate"));
				user.setGender(st.getInt("gender"));
				user.setId(st.getLong("id"));
				user.setModifyBy(st.getLong("modifyBy"));
				user.setModifyDate(st.getTimestamp("modifyDate"));
				user.setPhone(st.getString("phone"));
				user.setPicpath(st.getString("picpath"));
				user.setResume(st.getString("resume"));
				user.setUserCode(st.getString("userCode"));
				user.setUserName(st.getString("userName"));
				user.setUserPassword(st.getString("userPassword"));
				user.setUserRole(st.getInt("userRole"));
				userList.add(user);
			}
			return userList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return userList;
	}

	@Override
	public User login(User u) {
		String sql = " select * from `smbms_user` where userName = ? and userPassword = ? ; ";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setString(1, u.getUserName());
			ps.setString(2, u.getUserPassword());
			st = ps.executeQuery();
			User user = null;
			while (st.next()) {
				user = new User();
				user.setAddress(st.getString("address"));
				user.setBirthday(st.getTimestamp("birthday"));
				user.setCreatedBy(st.getLong("createdBy"));
				user.setCreationDate(st.getTimestamp("creationDate"));
				user.setGender(st.getInt("gender"));
				user.setId(st.getLong("id"));
				user.setModifyBy(st.getLong("modifyBy"));
				user.setModifyDate(st.getTimestamp("modifyDate"));
				user.setPhone(st.getString("phone"));
				user.setPicpath(st.getString("picpath"));
				user.setResume(st.getString("resume"));
				user.setUserCode(st.getString("userCode"));
				user.setUserName(st.getString("userName"));
				user.setUserPassword(st.getString("userPassword"));
				user.setUserRole(st.getInt("userRole"));
			}
			return user;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 批量删除
	@Override
	public Integer[] deleteBatch(String[] id) {
		String sql = "delete from smbms_user where id in (";
		for (int i = 0; i < id.length; i++) {
			if (i < id.length - 1) {
				sql += id[i] + ",";
			} else {
				sql += id[i];
			}
		}
		sql += ");";
		DBUtil db = new DBUtil();
		Integer[] i = db.cud(User.class, sql);
		return i;
	}

	// 单个删除
	@Override
	public int userRemove(String id) {
		String sql = "delete from smbms_user where id = ?";
		DBUtil db = new DBUtil();
		Integer[] i = db.cud(User.class, sql, id);
		if (i.length > 0) {
			return i[0];
		}
		return 0;
	}

	// @Test
	// public void T() {
	// System.out.println(getUserByPhone("13688889999"));
	// }

	@Override
	public List<User> getUserByid(String[] id) {
		String sql = " select * from smbms_user where id =? ;";
		List<User> list = new ArrayList<User>();
		if (id == null || id.length == 0) {
			return list;
		}
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < id.length; i++) {
				ps.setObject(1, id[i]);
				st = ps.executeQuery();
				while (st.next()) {
					User user = new User();
					user.setAddress(st.getString("address"));
					user.setBirthday(st.getTimestamp("birthday"));
					user.setCreatedBy(st.getLong("createdBy"));
					user.setCreationDate(st.getTimestamp("creationDate"));
					user.setGender(st.getInt("gender"));
					user.setId(st.getLong("id"));
					user.setModifyBy(st.getLong("modifyBy"));
					user.setModifyDate(st.getTimestamp("modifyDate"));
					user.setPhone(st.getString("phone"));
					user.setPicpath(st.getString("picpath"));
					user.setResume(st.getString("resume"));
					user.setUserCode(st.getString("userCode"));
					user.setUserName(st.getString("userName"));
					user.setUserPassword(st.getString("userPassword"));
					user.setUserRole(st.getInt("userRole"));
					list.add(user);
				}
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return list;
	}

	@Override
	public User getUserByUserCode(String userCode) {
		String sql = "select * from smbms_user where userCode=? ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, userCode);
			st = ps.executeQuery();
			User user = null;
			while (st.next()) {
				user = new User();
				user.setAddress(st.getString("address"));
				user.setBirthday(st.getTimestamp("birthday"));
				user.setCreatedBy(st.getLong("createdBy"));
				user.setCreationDate(st.getTimestamp("creationDate"));
				user.setGender(st.getInt("gender"));
				user.setId(st.getLong("id"));
				user.setModifyBy(st.getLong("modifyBy"));
				user.setModifyDate(st.getTimestamp("modifyDate"));
				user.setPhone(st.getString("phone"));
				user.setPicpath(st.getString("picpath"));
				user.setResume(st.getString("resume"));
				user.setUserCode(st.getString("userCode"));
				user.setUserName(st.getString("userName"));
				user.setUserPassword(st.getString("userPassword"));
				user.setUserRole(st.getInt("userRole"));
			}
			return user;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public User getUserByUserName(String userName) {
		String sql = "select * from smbms_user where userName=? ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, userName);
			st = ps.executeQuery();
			User user = null;
			while (st.next()) {
				user = new User();
				user.setAddress(st.getString("address"));
				user.setBirthday(st.getTimestamp("birthday"));
				user.setCreatedBy(st.getLong("createdBy"));
				user.setCreationDate(st.getTimestamp("creationDate"));
				user.setGender(st.getInt("gender"));
				user.setId(st.getLong("id"));
				user.setModifyBy(st.getLong("modifyBy"));
				user.setModifyDate(st.getTimestamp("modifyDate"));
				user.setPhone(st.getString("phone"));
				user.setPicpath(st.getString("picpath"));
				user.setResume(st.getString("resume"));
				user.setUserCode(st.getString("userCode"));
				user.setUserName(st.getString("userName"));
				user.setUserPassword(st.getString("userPassword"));
				user.setUserRole(st.getInt("userRole"));
			}
			return user;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public User getUserByPhone(String phone) {
		String sql = "select * from smbms_user where phone=? ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, phone);
			st = ps.executeQuery();
			User user = null;
			while (st.next()) {
				user = new User();
				user.setAddress(st.getString("address"));
				user.setBirthday(st.getTimestamp("birthday"));
				user.setCreatedBy(st.getLong("createdBy"));
				user.setCreationDate(st.getTimestamp("creationDate"));
				user.setGender(st.getInt("gender"));
				user.setId(st.getLong("id"));
				user.setModifyBy(st.getLong("modifyBy"));
				user.setModifyDate(st.getTimestamp("modifyDate"));
				user.setPhone(st.getString("phone"));
				user.setPicpath(st.getString("picpath"));
				user.setResume(st.getString("resume"));
				user.setUserCode(st.getString("userCode"));
				user.setUserName(st.getString("userName"));
				user.setUserPassword(st.getString("userPassword"));
				user.setUserRole(st.getInt("userRole"));
			}
			return user;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public User getUsersById(String id) {
		String sql = "select * from smbms_user where id=? ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, id);
			st = ps.executeQuery();
			User user = null;
			while (st.next()) {
				user = new User();
				user.setAddress(st.getString("address"));
				user.setBirthday(st.getTimestamp("birthday"));
				user.setCreatedBy(st.getLong("createdBy"));
				user.setCreationDate(st.getTimestamp("creationDate"));
				user.setGender(st.getInt("gender"));
				user.setId(st.getLong("id"));
				user.setModifyBy(st.getLong("modifyBy"));
				user.setModifyDate(st.getTimestamp("modifyDate"));
				user.setPhone(st.getString("phone"));
				user.setPicpath(st.getString("picpath"));
				user.setResume(st.getString("resume"));
				user.setUserCode(st.getString("userCode"));
				user.setUserName(st.getString("userName"));
				user.setUserPassword(st.getString("userPassword"));
				user.setUserRole(st.getInt("userRole"));
			}
			return user;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public int addUser(User user) {
		// 添加用户
		String sql = " insert into smbms_user ("//
				+ "userCode,userName,userPassword,"//
				+ "gender,birthday,phone,"//
				+ "address,userRole,picpath) values (?,?,?,?,?,?,?,?,?)";
		Map<String, Integer> map = cud1(sql, user.getUserCode(), user.getUserName(),
				EncodeUtil.enconding(user.getUserPassword()), user.getGender(), user.getBirthday(), user.getPhone(),
				user.getAddress(), user.getUserRole(), user.getPicpath());
		user.setId(new Long(map.get("generateKey")));
		System.out.println("添加完成的用户是>>>>>>>>>>>>>>>>>>>>>>" + user);
		return map.get("affectRows");
	}

	@Override
	public int updateUser(User user) {
		// 根据id修改对象
		String sql = " update smbms_user set  "//
				+ "userCode=?,userName=?,userPassword=?,"//
				+ "gender=?,birthday=?,phone=?,"//
				+ "address=?,userRole=?,picpath=? "//
				+ "where id =? ;";
		Map<String, Integer> map = cud1(sql, user.getUserCode(), user.getUserName(),
				EncodeUtil.enconding(user.getUserPassword()), user.getGender(), user.getBirthday(), user.getPhone(),
				user.getAddress(), user.getUserRole(), user.getPicpath(), user.getId());
		return map.get("affectRows");// 返回几条受影响
	}

	@Override
	public int UpdatePassWord(String userName, String passWord, String passWord1) {
		String sql = "update smbms_user set userPassword=? where userName=? and userPassword=? ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, passWord1);
			ps.setObject(2, userName);
			ps.setObject(3, passWord);
			int i = ps.executeUpdate();
			ct.commit();
			return i;
		} catch (Exception e) {
			try {
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			release(ct, ps, null);
		}
		return 0;
	}

	@Override
	public User getUserByUP(String userName, String passWord) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = " select * from `smbms_user` where userName = ? and userPassword = ? ; ";
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, userName);
			ps.setObject(2, passWord);
			st = ps.executeQuery();
			User u = null;
			while (st.next()) {
				u = new User();
				u.setAddress(st.getString("address"));
				u.setBirthday(st.getTimestamp("birthday"));
				u.setCreatedBy(st.getLong("createdBy"));
				u.setCreationDate(st.getTimestamp("creationDate"));
				u.setGender(st.getInt("gender"));
				u.setId(st.getLong("id"));
				u.setModifyBy(st.getLong("modifyBy"));
				u.setModifyDate(st.getTimestamp("modifyDate"));
				u.setPhone(st.getString("phone"));
				u.setPicpath(st.getString("picpath"));
				u.setResume(st.getString("resume"));
				u.setUserCode(st.getString("userCode"));
				u.setUserName(st.getString("userName"));
				u.setUserPassword(st.getString("userPassword"));
				u.setUserRole(st.getInt("userRole"));
			}
			return u;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

}
